Oracle SQL 性能调优：使用Hint固定执行计划2(Nested Loop Join)

2023-09-22 01:19| 来源: 网络整理| 查看: 265

编者按：

本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。

Oracle SQL 性能调优：使用Hint固定执行计划1(Hash Join)

Nested Loop Join 指定时用到的 Hint

和 Hash Join 相对应的，通常，利用索引时一般会用到 Nested Loop Join。

下面我们来继续看看如何控制 Nested Loop Join 的使用，以及 Nested Loop Join 的顺序。

LEADING Hint （指定 Nested Loop Join 顺序）

USE_NL (指定使用 Nested Loop Join)

依然通过例子来进行说明。

准备：

drop table t1 purge; drop table t2 purge; drop table t3 purge; create table t1(c1 number, c2 number); create table t2(c1 number, c2 number); create table t3(c1 number, c2 number); insert into t1 values (1,1); insert into t2 values (1,2); insert into t3 values (1,3); commit;

由于没有见索引，所以结合时会用到 Hash Join。

SQL> select /*+ leading(b a) */ * from t1 a, t2 b where a.c1=b.c1; C1 C2 C1 C2 ---------- ---------- ---------- ---------- 1 1 1 2 Execution Plan ---------------------------------------------------------- Plan hash value: 2959412835 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 52 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 52 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T1 | 1 | 26 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."C1"="B"."C1")

通过 USE_NL（内表内表） Hint 来指定的话，就用到了 Nested Loop Join，Leading（外表->内表->内表） Hint 用来指定结合顺序。

SQL> select /*+ leading(b a) USE_NL(a) */ * from t1 a, t2 b where a.c1=b.c1; C1 C2 C1 C2 ---------- ---------- ---------- ---------- 1 1 1 2 Execution Plan ---------------------------------------------------------- Plan hash value: 4016936828 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 52 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 52 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T1 | 1 | 26 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."C1"="B"."C1")

上面是2个表的情况，那么3个表的情况呢。

SQL> select /*+ leading(a b c) USE_NL(b c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1; C1 C2 C1 C2 C1 C2 ---------- ---------- ---------- ---------- ---------- ---------- 1 1 1 2 1 3 Execution Plan ---------------------------------------------------------- Plan hash value: 1998264463 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 78 | 9 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 78 | 9 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 52 | 6 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T1 | 1 | 26 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 3 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("A"."C1"="B"."C1") 5 - filter("A"."C1"="C"."C1")

【本文地址】

公司简介

联系我们